#!/usr/local/bin/perl -w

use App::Options (
    options => [qw(dbdriver dbclass dbhost dbname dbuser dbpass)],
    option => {
        dbclass  => { default => "App::Repository::MySQL", },
        dbdriver => { default => "mysql", },
        dbhost   => { default => "localhost", },
        dbname   => { default => "test", },
        dbuser   => { default => "", },
        dbpass   => { default => "", },
    },
);

use Test::More qw(no_plan);
use lib "../App-Context/lib";
use lib "../../App-Context/lib";
use lib "lib";
use lib "../lib";
use lib ".";
use lib "t";

use_ok("App");
use_ok("App::Repository");
use RepositoryTestUtils qw(create_table_test_person drop_table_test_person);
use strict;

if (!$App::options{dbuser}) {
    ok(1, "No dbuser given. Tests assumed OK. (add dbuser=xxx and dbpass=yyy to app.conf in 't' directory)");
    exit(0);
}

my $context = App->context(
    conf_file => "",
    conf => {
        Repository => {
            default => {
                class => $App::options{dbclass},
                dbdriver => $App::options{dbdriver},
                dbhost => $App::options{dbhost},
                dbname => $App::options{dbname},
                dbuser => $App::options{dbuser},
                dbpass => $App::options{dbpass},
                table => {
                    test_person => {
                        primary_key => ["person_id"],
                        alternate_key => [["first_name", "gender", "state"]],
                    },
                },
            },
        },
    },
    debug_sql => $App::options{debug_sql},
    trace => $App::options{trace},
);

my $db = $context->repository();
&drop_table_test_person($db);
&create_table_test_person($db);

{
    $db->_load_rep_metadata();

    my $dbtype = $App::options{dbtype};
    if ($dbtype eq "oracle") {
        ok(1, "These tests are not for Oracle");
    }
    else {
        my ($nrows, $id);
        my ($expect_sql, $sql);
        ok($db->_insert_row("test_person", ["person_id","age","first_name","gender","state"],
            [1,39,"stephen",  "M","GA"]),
            "insert row (primary key included)");
        ok($db->_insert_row("test_person", ["age","first_name","gender","state"],
            [37,"susan",    "F","GA"]),
            "insert row (primary key excluded, auto_increment)");
        ok($db->_insert_row("test_person", ["person_id","age","first_name","gender","state"],
            [undef, 6,"maryalice","F","GA"]),
            "insert row (primary key included, null)");
        ok($db->_insert_row("test_person", ["person_id","age","first_name","gender","state"],
            [0, 3,"paul",     "M","GA"]),
            "insert row (primary key included, 0)");
#        ok($db->_insert_row("test_person", ["person_id","age","first_name","gender","state"],
#            [5, 1,"christine","F","GA"]),
#            "insert again");
#        ok($db->_insert_row("test_person", ["person_id","age","first_name","gender","state"],
#            [6,45,"tim",      "M","GA"]),
#            "insert again");
#        ok($db->_insert_row("test_person", ["person_id","age","first_name","gender","state"],
#            [7,39,"keith",    "M","GA"]),
#            "insert again");

        $expect_sql = <<EOF;
insert into test_person
  (age,
   first_name,
   gender,
   state)
values
  (?,
   ?,
   ?,
   ?)
EOF
        ok($db->_insert_row("test_person", ["age","first_name","gender","state"],
            { age => 1, first_name => "christine", gender => "F", state => "GA" }),
            "insert again (cols, values as hash)");
        is($db->{sql}, $expect_sql, "insert row. sql ok");

        $nrows = $db->_insert_row("test_person", ["age","first_name","gender","state"],
            { age => 45, first_name => "tim", gender => "M", state => "GA" }, {last_inserted_id => 1});
        ok($nrows, "insert again (cols, values as hash, returning last_inserted_id)");
        $id = $db->_last_inserted_id("test_person");
        is($id, 6, "insert again (cols, values as hash, returning last_inserted_id) got id=6");
        is($db->{sql}, $expect_sql, "insert again (cols, values as hash, returning last_inserted_id) sql ok");

        $nrows = $db->_insert_row("test_person",
            { age => 39, first_name => "keith", gender => "M", state => "GA" }, undef, {last_inserted_id => 1}),
        ok($nrows, "insert again (cols/values as single hash, returning last_inserted_id)");
        $id = $db->_last_inserted_id("test_person");
        is($id, 7, "insert again (cols/values as single hash, returning last_inserted_id) got id=7");

        ok($db->insert("test_person", {
                person_id => 8,
                age => 35,
                first_name => "alex",
                gender => "M",
                state => "GA",
            }),
            "insert hash");
        eval {
            $db->insert_row("test_person", {
                person_id => 8,
                age => 35,
                first_name => "alex2",
                gender => "M",
                state => "GA",
            });
        };
        ok($@, "insert dup hash fails");
        ok($db->insert("test_person", undef, {
                person_id => 9,
                age => 35,
                first_name => "alex3",
                gender => "M",
                state => "GA",
            }),
            "insert hash in 2nd pos");
        ok($db->insert("test_person", ["age","first_name","gender","state"], {
                person_id => 9,
                age => 35,
                first_name => "alex4",
                gender => "M",
                state => "GA",
            }),
            "insert hash in 2nd pos w/ col spec");
        eval {
            $db->insert_row("test_person", undef, {
                person_id => 9,
                age => 35,
                first_name => "alex5",
                gender => "M",
                state => "GA",
            });
        };
        ok($@, "insert dup hash in 2nd pos fails");

        ok($db->insert("test_person", undef, {
                person_id => 12,
                age => 999,
                first_name => '%@$\\\'',
                gender => "M",
                state => "GA",
            }),
            "insert \\ and ' and \\' seems to work");
        
        is($db->get("test_person",12,"first_name"),'%@$\\\'', "yep. first_name worked.");

        my $new_hashes =
           [{ age=>39, first_name=>"stephen", gender=>"M", state=>"GA", foo=>"bar"},
            { age=>37, first_name=>"susan", gender=>"F", state=>"GA", foo=>"bar"},
            { age=>6, first_name=>"maryalice", gender=>"F", state=>"GA", foo=>"bar"},
            { age=>3, first_name=>"paul", gender=>"M", state=>"GA", foo=>"bar"},
            { age=>1, first_name=>"christine", gender=>"F", state=>"GA", foo=>"bar"},
            { age=>45, first_name=>"tim", gender=>"M", state=>"GA", foo=>"bar"},
            { age=>39, first_name=>"keith", gender=>"M", state=>"GA", foo=>"bar"},];

        my $new_rows =
           [[39,"stephen",  "M","CA"],
            [37,"susan",    "F","CA"],
            [6,"maryalice", "F","CA"],
            [3,"paul",      "M","CA"],
            [1,"christine", "F","CA"],
            [45,"tim",      "M","CA"],
            [39,"keith",    "M","CA"],];

        my $dup_rows =
           [[1, 39,"stephen",  "M","GA"],
            [2, 37,"susan",    "F","GA"],
            [3, 6,"maryalice", "F","GA"],
            [4, 3,"paul",      "M","GA"],
            [5, 1,"christine", "F","GA"],
            [6, 45,"tim",      "M","GA"],
            [7, 39,"keith",    "M","GA"],];

    $expect_sql = <<EOF;
insert into test_person
  (age, first_name, gender, state)
values
  (39, 'stephen', 'M', 'CA'),
  (37, 'susan', 'F', 'CA'),
  (6, 'maryalice', 'F', 'CA'),
  (3, 'paul', 'M', 'CA'),
  (1, 'christine', 'F', 'CA'),
  (45, 'tim', 'M', 'CA'),
  (39, 'keith', 'M', 'CA')
EOF
    $sql = $db->_mk_insert_rows_sql("test_person", ["age","first_name","gender","state"], $new_rows);
    is($sql, $expect_sql, "_mk_insert_rows_sql(): 7 rows, bulk insert");
    $expect_sql =~ s/CA/GA/g;
    $sql = $db->_mk_insert_rows_sql("test_person", ["age","first_name","gender","state"], $new_hashes);
    is($sql, $expect_sql, "_mk_insert_rows_sql(): 7 rows, bulk insert (from hashes)");

    $expect_sql = <<EOF;
replace into test_person
  (age, first_name, gender, state)
values
  (39, 'stephen', 'M', 'CA'),
  (37, 'susan', 'F', 'CA'),
  (6, 'maryalice', 'F', 'CA'),
  (3, 'paul', 'M', 'CA'),
  (1, 'christine', 'F', 'CA'),
  (45, 'tim', 'M', 'CA'),
  (39, 'keith', 'M', 'CA')
EOF
        $sql = $db->_mk_insert_rows_sql("test_person", ["age","first_name","gender","state"], $new_rows, { replace => 1 });
        is($sql, $expect_sql, "_mk_insert_rows_sql(): 7 rows, bulk replace");

        $expect_sql = <<EOF;
insert into test_person
  (person_id, age, first_name, gender, state)
values
  (1, 39, 'stephen', 'M', 'GA'),
  (2, 37, 'susan', 'F', 'GA'),
  (3, 6, 'maryalice', 'F', 'GA'),
  (4, 3, 'paul', 'M', 'GA'),
  (5, 1, 'christine', 'F', 'GA'),
  (6, 45, 'tim', 'M', 'GA'),
  (7, 39, 'keith', 'M', 'GA')
on duplicate key update
   person_id = values(person_id),
   age = values(age),
   first_name = values(first_name),
   gender = values(gender),
   state = values(state)
EOF
        $sql = $db->_mk_insert_rows_sql("test_person", ["person_id", "age","first_name","gender","state"], $dup_rows, { update => 1 });
        is($sql, $expect_sql, "_mk_insert_rows_sql(): 7 rows, bulk insert/update");

        $expect_sql = <<EOF;
insert into test_person
  (person_id, age, first_name, gender, state)
values
  (1, 39, 'stephen', 'M', 'GA'),
  (2, 37, 'susan', 'F', 'GA'),
  (3, 6, 'maryalice', 'F', 'GA'),
  (4, 3, 'paul', 'M', 'GA'),
  (5, 1, 'christine', 'F', 'GA'),
  (6, 45, 'tim', 'M', 'GA'),
  (7, 39, 'keith', 'M', 'GA')
on duplicate key update
   age = values(age),
   gender = values(gender),
   state = values(state)
EOF
        $sql = $db->_mk_insert_rows_sql("test_person", ["person_id", "age","first_name","gender","state"], $dup_rows, { update => { age => 1, gender => 1, state => 1} });
        is($sql, $expect_sql, "_mk_insert_rows_sql(): 7 rows, bulk insert/update, with named columns");

        $expect_sql = <<EOF;
insert into test_person
  (person_id, age, first_name, gender, state)
values
  (1, 39, 'stephen', 'M', 'GA'),
  (2, 37, 'susan', 'F', 'GA'),
  (3, 6, 'maryalice', 'F', 'GA'),
  (4, 3, 'paul', 'M', 'GA'),
  (5, 1, 'christine', 'F', 'GA'),
  (6, 45, 'tim', 'M', 'GA'),
  (7, 39, 'keith', 'M', 'GA')
on duplicate key update
   age = (case when values(gender) = 'F' then age-1 else values(age) end),
   gender = values(gender),
   state = values(state)
EOF
        $sql = $db->_mk_insert_rows_sql("test_person", ["person_id", "age","first_name","gender","state"], $dup_rows, { update => { age => "(case when values(gender) = 'F' then age-1 else values(age) end)", gender => 1, state => 1} });
        is($sql, $expect_sql, "_mk_insert_rows_sql(): 7 rows, bulk insert/update, with named columns and custom expressions");

        $nrows = $db->insert_rows("test_person", ["age","first_name","gender","state"], $new_rows);
        is($nrows, 7, "insert_rows(): 7 rows, bulk insert");
        $nrows = $db->insert_rows("test_person", ["person_id","age","first_name","gender","state"], $dup_rows, { replace => 1 });
        is($nrows, 7, "insert_rows(): 7 rows, bulk replace");
        $nrows = $db->insert_rows("test_person", ["person_id", "age","first_name","gender","state"], $dup_rows, { update => 1 });
        is($nrows, 7, "insert_rows(): 7 rows, bulk insert/update");
        $nrows = $db->insert_rows("test_person", ["person_id", "age","first_name","gender","state"], $dup_rows, { update => { age => 1, gender => 1, state => 1} });
        is($nrows, 7, "insert_rows(): 7 rows, bulk insert/update w/ named columns");
        $nrows = $db->insert_rows("test_person", ["person_id", "age","first_name","gender","state"], $dup_rows, { update => { age => "(case when values(gender) = 'F' then age-1 else values(age) end)", gender => 1, state => 1} });
        is($nrows, 7, "insert_rows(): 7 rows, bulk insert/update w/ named columns and custom expressions");
        $nrows = $db->insert_rows("test_person", ["person_id","age","first_name","gender","state"], $dup_rows, { replace => 1, maxrows => 4 });
        is($nrows, 7, "insert_rows(): 7 rows, bulk replace (4 at a time)");
        $nrows = $db->insert_rows("test_person", ["person_id", "age","first_name","gender","state"], $dup_rows, { update => 1, maxrows => 4 });
        is($nrows, 7, "insert_rows(): 7 rows, bulk insert/update (4 at a time)");
        $nrows = $db->insert_rows("test_person", [{first_name => "Steve", state => "GA", gender => "M", age => 47 }], [], {update => 1});
        is($nrows, 1, "insert_rows(): 1 rows, array of hashes");
    }

    my ($key_idx, $columns, $row, $nrows);

    $columns = [ "age", "person_id", "last_name", "first_name", "gender", "state" ];
    $row     = [ 40,    1,           "adkins",    "stephen",    "M",      "GA" ];
    $key_idx = $db->_key_idx("test_person", $columns);
    is(ref($key_idx), "ARRAY", "key_idx is ARRAY");
    is($key_idx->[0], 1, "key_idx is [1] for [@$columns]");
    $nrows = $db->insert_row("test_person",$columns,$row,{update => 1});
    is($nrows, 1, "insert with {update} option on primary key");
    is($db->get("test_person",1,"age"), 40, "updated age to 40 successful");

    $columns = [ "age", "last_name", "first_name", "state", "gender" ];
    $row     = [ 41,    "zadkins",   "stephen",    "GA",    "M" ];
    $key_idx = $db->_key_idx("test_person", $columns);
    is(ref($key_idx), "ARRAY", "key_idx is ARRAY");
    is($#$key_idx, 2, "key_idx is 3 elements [@$key_idx]");
    is($key_idx->[0], 2, "key_idx[0] is 2 for [@$columns]");
    is($key_idx->[1], 4, "key_idx[1] is 4 for [@$columns]");
    is($key_idx->[2], 3, "key_idx[2] is 3 for [@$columns]");
    $nrows = $db->insert_row("test_person",$columns,$row,{update => {age => 1, last_name => 1}});
    is($nrows, 1, "insert with {update} option on alternate key");
    is($db->get("test_person",1,"age"), 41, "updated age to 41 successful");
    is($db->get("test_person",1,"last_name"), "zadkins", "updated last_name to zadkins successful");
}

exit 0;

